
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[usp_DeleteEmployer]
    (
      @JOL_Employer_Account VARCHAR(50)
    )
AS 


    DECLARE @No INT,
        @count INT
    SET @count = (
                   SELECT   COUNT(JOL_Field_No)
                   FROM     dbo.JOL_ApplyField
                   WHERE    JOL_Field_ApplyCompany = @JOL_Employer_Account
                 )
    DECLARE delcur SCROLL CURSOR
        FOR SELECT  JOL_Lecture_Purpose
            FROM    dbo.JOL_Lecture AS a
                    INNER JOIN dbo.JOL_ApplyField AS b ON a.JOL_Lecture_Purpose = b.JOL_Field_No
            WHERE   b.JOL_Field_ApplyCompany = @JOL_Employer_Account
        FOR UPDATE
    OPEN delcur
    FETCH FIRST FROM delcur
    WHILE @count > 0
        BEGIN
			
            DELETE  FROM dbo.JOL_Lecture
            WHERE CURRENT OF delcur
            FETCH NEXT FROM delcur
            SET @count = @count - 1
        END
    CLOSE delcur


----删除与该公司相关的宣讲会、场地申请、需求信息、公务员考试信息
--            DELETE  FROM dbo.JOL_Lecture
--            WHERE   JOL_Lecture_Purpose = @No
--            SET @count = @count - 1
            
--            SELECT  JOL_Field_No
--            FROM    dbo.JOL_ApplyField
--            WHERE   JOL_Field_ApplyCompany = @JOL_Employer_Account

--        END
    
    DELETE  FROM dbo.JOL_ApplyField
    WHERE   JOL_Field_ApplyCompany = @JOL_Employer_Account
	
    DELETE  FROM dbo.JOL_Require
    WHERE   JOL_Require_Company = @JOL_Employer_Account
    
    DELETE  FROM dbo.JOL_GWY_News
    WHERE   JOL_EmployerName = @JOL_Employer_Account
	
    DELETE  FROM dbo.JOL_Employer
    WHERE   JOL_Employer_Account = @JOL_Employer_Account
GO
